package crm.control;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import crm.dbcoon.DBConnect;
import crm.model.Notice;
import crm.util.Pagination;

public class NoticeControl {
	public static Pagination pageInstance = null;
	
    //获取一条动态
    public Notice getOneNoticeById(int id){
 	   DBConnect dbc = new DBConnect();
 	   String sql = "select * from common_inform where id = "+id;
 	   Notice notice = new Notice();
 	   dbc.prepareStatement(sql);
 	   try{
 		   ResultSet rs = dbc.executeQuery();
 		   while(rs.next()){
 			  notice.setId(id);
 			  notice.setTheme(rs.getString("theme"));
 			  notice.setTime(rs.getString("time"));
 			  notice.setWriter(rs.getInt("writer"));
 		   	  notice.setContent(rs.getString("content"));
 			      
 		   }
 	   }
 		   catch(SQLException e){
 			   e.printStackTrace();
 		   }
 		   finally{
 			   dbc.close();
 		   }
 		   return notice;
 	   }
    
	// 增加公告通知
	public boolean insertNotice(Notice notice) {
		DBConnect dbc = new DBConnect();
		String sql = "insert into common_inform(theme,time,writer,content)values(?,?,?,?)";
		dbc.prepareStatement(sql);
		try {
			// dbc.setInt(1,thought.getId());
			dbc.setString(1, notice.getTheme());
			dbc.setString(2, notice.getTime());
			dbc.setInt(3,notice.getWriter());
			dbc.setString(4,notice.getContent());
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}

	// 根据id删除公告通知
	public boolean deleteNotice(int id) {
		DBConnect dbc = new DBConnect();
		String sql = "delete from common_inform where id=?";
		dbc.prepareStatement(sql);
		try {
			dbc.setInt(1, id);
			dbc.executeUpdate();
			System.out.print("akjsdl;");
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}

	// 根据id修改公告通知
	public boolean updateNotice(Notice notice, int id) {
		DBConnect dbc = new DBConnect();
		String sql = "update common_inform set theme='" + notice.getTheme()
				+ "',time='" + notice.getTime() + "',writer='" + notice.getWriter() + "',content='"
				+ notice.getContent() + "'where id=" + id;
		dbc.prepareStatement(sql);
		try {
			dbc.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			dbc.close();
		}
	}
	
	// 分页显示
	public Collection GetNotice(int page, int rowsPerPage, String addtime)
			throws SQLException, Exception {
		String sql = "SELECT * FROM common_inform order by " + addtime + " desc";
		pageInstance = new NoticeControlPage(sql, rowsPerPage);
		return pageInstance.getPage(page);
	}

	class NoticeControlPage extends Pagination {
		public NoticeControlPage(String sql, int rowsPerPage) {
			super(sql, rowsPerPage, 1);
		}

		protected Collection<Notice> packResultSet(ResultSet rs)
				throws SQLException {
			Collection<Notice> NoticeList = new ArrayList<Notice>();
			while (rs.next()) {
				Notice s = new Notice();
				s.setId(rs.getInt("id"));
				s.setTheme(rs.getString("theme"));
				s.setTime(rs.getString("time"));
				s.setWriter(rs.getInt("writer"));
				s.setContent(rs.getString("content"));
				NoticeList.add(s);
				s = null;
			}
			return NoticeList;
		}
	}

}
